package com.leoman.utils;

import com.leoman.entity.vo.QuestionVo;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016/4/1 0001.
 */
public class ReadExcelUtil {

    public static void readXls() {
        try {
            File file = new File("E:/导入新增高校.xlsx");
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

            int rowstart = hssfSheet.getFirstRowNum();
            int rowEnd = hssfSheet.getLastRowNum();
            for (int i = rowstart; i <= rowEnd; i++) {
                HSSFRow row = hssfSheet.getRow(i);
                if (null == row) continue;
                int cellStart = row.getFirstCellNum();
                int cellEnd = row.getLastCellNum();

                for (int k = cellStart; k <= cellEnd; k++) {
                    HSSFCell cell = row.getCell(k);
                    if (null == cell) continue;
                    System.out.println("" + k + "  ");
                    System.out.println("type:" + cell.getCellType());

                    chooseValue(cell.getCellType(), null, cell, 1);

                }
                System.out.print("\n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void readXlsx() {
        try {
            File file = new File("E:/导入新增高校.xlsx");
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

            int rowstart = xssfSheet.getFirstRowNum();
            int rowEnd = xssfSheet.getLastRowNum();
            for (int i = rowstart; i <= rowEnd; i++) {
                XSSFRow row = xssfSheet.getRow(i);
                if (null == row) continue;
                int cellStart = row.getFirstCellNum();
                int cellEnd = row.getLastCellNum();

                for (int k = cellStart; k <= cellEnd; k++) {
                    XSSFCell cell = row.getCell(k);
                    if (null == cell) continue;

                    chooseValue(cell.getCellType(), cell, null, 0);
                }
                System.out.print("\n");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void chooseValue(Integer cellType, XSSFCell xssfCell, HSSFCell hssfCell, Integer type) {
        switch (cellType) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                if (type == 1) {
                    System.out.print(hssfCell.getNumericCellValue() + "   ");
                } else {
                    System.out.print(xssfCell.getNumericCellValue() + "   ");
                }
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                if (type == 1) {
                    System.out.print(hssfCell.getStringCellValue() + "   ");
                } else {
                    System.out.print(xssfCell.getStringCellValue() + "   ");
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                if (type == 1) {
                    System.out.print(hssfCell.getBooleanCellValue() + "   ");
                } else {
                    System.out.print(xssfCell.getBooleanCellValue() + "   ");
                }
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                if (type == 1) {
                    System.out.print(hssfCell.getCellFormula() + "   ");
                } else {
                    System.out.print(xssfCell.getCellFormula() + "   ");
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                System.out.println(" ");
                break;
            case HSSFCell.CELL_TYPE_ERROR: // 故障
                System.out.println(" ");
                break;
            default:
                System.out.print("未知类型   ");
                break;
        }
    }

    public static List<QuestionVo> readExcel(MultipartFile multipartFile) {
        List<QuestionVo> list = new ArrayList<>();
        QuestionVo questionVo;

        try {
            CommonsMultipartFile cf = (CommonsMultipartFile) multipartFile;
            DiskFileItem fi = (DiskFileItem) cf.getFileItem();
            File file = fi.getStoreLocation();
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

            int rowEnd = hssfSheet.getLastRowNum();
            for (int i = 2; i <= rowEnd; i++) {
                HSSFRow row = hssfSheet.getRow(i);
                if (null == row) continue;

                if (null == row.getCell(1) || null == row.getCell(2)) continue;

                // 将数据写入map
                questionVo = new QuestionVo();
                questionVo.setId(getIntNum(row.getCell(0)));
                questionVo.setType(getString(row.getCell(1)));
                questionVo.setRecordDate(getString(row.getCell(2)));
                questionVo.setArea(getString(row.getCell(3)));
                questionVo.setDescriptionOne(getString(row.getCell(4)));
                questionVo.setImages(getString(row.getCell(5)));
                questionVo.setDescriptionTwo(getString(row.getCell(6)));
                questionVo.setScoreOne(getIntNum(row.getCell(7)));
                questionVo.setScoreTwo(getIntNum(row.getCell(8)));
                questionVo.setScoreThree(getIntNum(row.getCell(9)));
                questionVo.setScoreFour(getIntNum(row.getCell(10)));
                questionVo.setScoreFive(getIntNum(row.getCell(11)));
                questionVo.setScoreSix(getIntNum(row.getCell(12)));
                questionVo.setScoreSeven(getIntNum(row.getCell(13)));
                questionVo.setNameOne(getString(row.getCell(14)));
                questionVo.setSectionOne(getString(row.getCell(15)));
                questionVo.setDescriptionThree(getString(row.getCell(16)));
                questionVo.setScoreEight(getIntNum(row.getCell(17)));
                questionVo.setScoreNine(getIntNum(row.getCell(18)));
                questionVo.setScoreTen(getIntNum(row.getCell(19)));
                questionVo.setSectionTwo(getString(row.getCell(20)));
                questionVo.setNameTwo(getString(row.getCell(21)));
                questionVo.setPlanDateOne(getString(row.getCell(22)));
                questionVo.setActualDateOne(getString(row.getCell(23)));
                questionVo.setDescriptionFour(getString(row.getCell(24)));
                questionVo.setScoreEleven(getIntNum(row.getCell(25)));
                questionVo.setScoreTwelve(getIntNum(row.getCell(26)));
                questionVo.setScoreThirteen(getIntNum(row.getCell(27)));
                questionVo.setSectionThree(getString(row.getCell(28)));
                questionVo.setNameThree(getString(row.getCell(29)));
                questionVo.setPlanDateTwo(StringUtils.isNotBlank(row.getCell(30).getStringCellValue()) ? row.getCell(30).getStringCellValue() : DateUtils.longToString(System.currentTimeMillis(), "yyyy-MM-dd"));
                questionVo.setActualDateTwo(StringUtils.isNotBlank(row.getCell(31).getStringCellValue()) ? row.getCell(31).getStringCellValue() : DateUtils.longToString(System.currentTimeMillis(), "yyyy-MM-dd"));
                questionVo.setIsCut(getString(row.getCell(32)));
                questionVo.setStatus(getString(row.getCell(33)));

                list.add(questionVo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    public static int getIntNum(HSSFCell cell) {
        // CELL_TYPE_NUMERIC 数值型 0
        // CELL_TYPE_STRING 字符串型 1
        // CELL_TYPE_FORMULA 公式型 2
        // CELL_TYPE_BLANK 空值 3
        // CELL_TYPE_BOOLEAN 布尔型 4
        // CELL_TYPE_ERROR 错误 5

        try {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK || cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
                return 0;
            } else {
                try {
                    return (int) cell.getNumericCellValue();
                } catch (Exception e) {
                    return 0;
                }
            }
        } catch (Exception e) {
            return 0;
        }
    }

    public static String getString(HSSFCell cell) {
        // CELL_TYPE_NUMERIC 数值型 0
        // CELL_TYPE_STRING 字符串型 1
        // CELL_TYPE_FORMULA 公式型 2
        // CELL_TYPE_BLANK 空值 3
        // CELL_TYPE_BOOLEAN 布尔型 4
        // CELL_TYPE_ERROR 错误 5

        try {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK || cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
                return "";
            } else {
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    return "";
                }
            }
        } catch (Exception e) {
            return "";
        }
    }
}
